<?php

/**
 * Created by PhpStorm.
 * User: fengtian
 * Date: 15-3-20
 * Time: 下午12:13
 */
class Checkstock extends Basic
{
    public static function getList($items_per_page,$offset,$pre)
    {
        return $pre->limit($items_per_page)
            ->offset($offset)
            ->execute('newerp')
            ->as_array();
    }

    /**
     * Description 查询某一时间段入库的总和
     * @param          field_type  bare_field_name
     * @param array    $data
     * @param datetime $time
     * @return int  某一时间段入库数量
     */
    public static function getIn($data,$time)
    {
        $time   = date('Y-m-d',strtotime("+1 day",strtotime($time)));
        $in_num = DB::select(DB::expr('SUM(warehouseorders.warehouse_quantity) as in_num'))
            ->from('warehouseorders')
            ->where('sku','=',$data['sku'])
            ->and_where('warehouse_date','>=',$data['inventory_date'])
            ->and_where('warehouse_date','<',$time)
            ->execute('newerp')
            ->get('in_num',0);
        return $in_num;
    }

    /**
     * Description 查询某一时间段出库的总和
     * @param          field_type  bare_field_name
     * @param array    $data
     * @param datetime $time
     * @return int  某一时间段出库数量
     */
    public static function getOut($data,$time)
    {
        $time    = date('Y-m-d',strtotime("+1 day",strtotime($time)));
        $out_num = DB::select(DB::expr('SUM(deliveryorders.quantity) as out_num'))
            ->from('deliveryorders')
            ->where('sku','=',$data['sku'])
            ->and_where('delivery_date','>=',$data['inventory_date'])
            ->and_where('delivery_date','<',$time)
            ->execute('newerp')
            ->get('out_num',0);
        return $out_num;
    }

    //获取老erp扫描已出库，但库存数据还没同步的库存量
    public static function load_lazy_deliverry($sku,$time)
    {
        $start_time = date('Y-m-d H:00:00',strtotime($time));
        $totle      = DB::select(DB::expr('SUM(item_count)AS totle'))
            ->from('erp_orders')
            ->join('erp_orders_products')
            ->on('erp_orders.erp_orders_id','=','erp_orders_products.erp_orders_id')
            ->where('orders_sku','=',$sku)
            ->and_where('orders_out_time','>=',$start_time)
            ->and_where('orders_out_time','<=',$time)
            ->and_where('orders_status','=',5)
            ->execute('readonlyerp')
            ->get('totle',0);
        return $totle;
    }

    public static function excute_data($datas,$time)
    {

        for($i = 0;$i<count($datas);$i++){
            $in_nums               = self::getIn($datas[$i],$time);
            $out_num               = self::getOut($datas[$i],$time);
            $lazy                  = self::load_lazy_deliverry($datas[$i]['sku'],date("Y-m-d H:i:s"));
            $datas[$i]['quantity'] = $datas[$i]['quantity']+$in_nums-$out_num-$lazy;
            $datas[$i]['lazy']     = $lazy;
        }
        return $datas;
    }

    public static function getTotlePre($tablename)
    {
        return DB::select(DB::expr('COUNT(id)AS totle'))
            ->from($tablename);
    }

    public static function getListInfoCondition($pre,$condition,$flag = TRUE,$tablename)
    {

        if(!empty($condition['storage_position1'])&&!empty($condition['storage_position2'])){
            $condition1 = array(
                'storage_position',
                '>=',
                trim($condition['storage_position1'])*10000
            );
            $pre        = self::condition($pre,$condition1);
            $condition1 = array(
                'storage_position',
                '<=',
                (trim($condition['storage_position2'])+1)*10000
            );
            $pre        = self::condition($pre,$condition1);
        }

        if(!empty($condition['storage_position1'])&&empty($condition['storage_position2'])){
            $condition1 = array(
                'storage_position',
                'like',
                '%'.trim($condition['storage_position1']).'%'
            );
            $pre        = self::condition($pre,$condition1);
        }

        if(empty($condition['storage_position1'])&&!empty($condition['storage_position2'])){
            $condition1 = array(
                'storage_position',
                'like',
                '%'.trim($condition['storage_position2']).'%'
            );
            $pre        = self::condition($pre,$condition1);
        }

        if(!empty($condition['storage'])){
            $condition1 = array(
                'storage_position',
                'like',
                trim($condition['storage']).'%'
            );
            $pre        = self::condition($pre,$condition1);
        }
        if(isset($condition['sku'])&&!empty($condition['sku'])){
            $condition1 = array(
                'sku',
                '=',
                trim($condition['sku'])
            );
            $pre        = self::condition($pre,$condition1);
        }
        if(isset($condition['start_time'])&&!empty($condition['start_time'])){
            $firstday   = date('Y-m-01',strtotime($condition['start_time']));
            $condition1 = array(
                'inventory_date',
                '=',
                $firstday
            );
            $pre        = self::condition($pre,$condition1);
        }
        if(isset($condition['supplier'])&&!empty($condition['supplier'])){
            $condition1 = array(
                'supplier_id',
                '=',
                $condition['supplier']
            );
            $pre        = self::condition($pre,$condition1);
        }
        if($flag){
            if(isset($condition['sortcondition'])&&!empty($condition['sortcondition'])){

                switch($condition['sortcondition']){
                    case 1:
                        $value = 'sku';
                        break;
                    case 2:
                        $value = 'supplier_id';
                        break;
                    case 3:
                        $value = 'quantity';
                        break;
                    case 4:
                        $value = 'storage_position';
                        break;
                }
                $sort = $condition['order']==1?'ASC':'DESC';
                $pre  = $pre->order_by($value,$sort);
            }
        }
        return $pre;
    }

    //筛选条件
    public static function condition($re,$get)
    {
        if($get['status'])$re->where('l.status','=',$get['status']);

        if(!empty($get['storage1'])&&!empty($get['storage2']))
        {
            $re->where('i.storage_position','>=',trim($get['storage1'])*10000);
            $re->where('i.storage_position','<=',trim($get['storage2'])*10000);
        }

        if(!empty($get['storage1'])&&empty($get['storage2']))
            $re->where('i.storage_position','like','%'.trim($get['storage1'])).'%';


        if(empty($get['storage1'])&&!empty($get['storage2']))
            $re->where('i.storage_position','like','%'.trim($get['storage2'])).'%';

        if(!empty($get['storage']))
            $re->where('i.storage_position','like',trim($get['storage']).'%');

        if(isset($get['sku'])&&!empty($get['sku']))
            $re->where('i.sku','=',trim($get['sku']));

        $re->where('i.inventory_date','=',date('Y-m-01',strtotime($get['start_time'])));

        if(isset($get['sort'])&&!empty($get['sort']))
        {
            switch ($get['sort'])
            {
                case 1:
                    $value='l.sale';
                    break;
                case 2:
                    $value='i.sku';
                    break;
                case 3:
                    $value='i.storage_position';
                    break;
            }
            $get['order']==1?$sort='DESC':$sort='ASC';
            $re->order_by($value,$sort);
        }

        return $re;
    }

    //拼凑列表数据
    public static function install_lists($begin,$end,$lists,$f=false)
    {
        $infos = DB::select('id',DB::expr('upper(sku) as sku'),'sku_name','storage_position','quantity')
            ->from('inventories')->where('sku','in',$lists)->where('inventory_date','=',$begin)
            ->execute('newerp')->as_array('sku');


        //是否借料
        $lends = self::getLendsNums($lists);

        $dels = self::get_delivery_nums($begin,$end,$lists);
        $wars = self::get_warehouse_nums($begin,$end,$lists);
        $f?$hols = Warningtmp::get_print_num($lists):'';

        /*$client = new PHPRPC_Client();
        $client->useService("http://192.168.5.5:802/profitapi/execute");*/

        //成本价
        //$sri = "select `id`,`status` from `statuss`";
        $sri = "select `id`,`status` from `skusystem_statuss`";
        //$ts = json_decode($client->getinfo($sri),TRUE);
        $ts = DB::query(Database::SELECT,$sri)->execute('newerp')->as_array();
        //获得实时状态
        /*$sku_sts = DB::select('sku','status_id')->from('status_skuss')
            ->where('sku', 'IN', $lists)
            ->compile('newerp');*/

        $sku_sts = "SELECT sku,status_id FROM system_status_skuss WHERE sku in ('".join("','",$lists)."')";
        $sku_sts = DB::query(Database::SELECT,$sku_sts)->execute('newerp')->as_array();
        //$sku_sts = json_decode($client->getinfo($sku_sts),TRUE);
        $sts = array();
        foreach($sku_sts as $s) {
            $sts[$s['sku']] = $s['status_id'];
        }

        $lists = DB::select('sku','sale','onsale','status')->from("ldt_skus")
            ->where('sku','in',$lists)->order_by('sale','DESC')
            ->execute('newerp')->as_array('sku');

        $types = array();
        foreach($ts as $t){
            $types[$t['id']] = $t['status'];
        }

        foreach($lists as &$list)
        {
            $list['sku_name']   = isset($infos[$list['sku']]['sku_name'])?$infos[$list['sku']]['sku_name']:0;
            $list['storage']    = isset($infos[$list['sku']]['storage_position'])?$infos[$list['sku']]['storage_position']:0;
            $list['quantity']   = isset($infos[$list['sku']]['quantity'])?$infos[$list['sku']]['quantity']:0;
            $list['in_id']      = isset($infos[$list['sku']]['id'])?$infos[$list['sku']]['id']:0;

            $del    = isset($dels[$list['sku']])&&$dels[$list['sku']]?$dels[$list['sku']]:0;
            $war    = isset($wars[$list['sku']])&&$wars[$list['sku']]?$wars[$list['sku']]:0;

            $list['qichu']      = $list['quantity'];
            $list['benqiru']    = $war;
            $list['benqichu']   = $del;
            $list['quantity']   = $list['quantity'] + $war - $del;
            $list['lend']       = isset($lends[$list['sku']])?$lends[$list['sku']]:0;
            $list['onsale_name']= isset($sts[$list['sku']])?$types[$sts[$list['sku']]]:'未知状态';
            if($f)
            {
                $list['hold']   = isset($hols[$list['sku']])?$hols[$list['sku']]:'';
            }
        }

        return $lists;
    }

    public static function getLendsNums ($skus = NULL) {
        if(!empty($skus)) {
            return DB::select(DB::expr("upper(sku) as sku"),DB::expr('sum(num) as num'))->from('bymaterials')
                ->where('status', '=', 0)
                ->where('sku', 'IN', $skus)
                ->group_by('sku')
                ->execute('newerp')
                ->as_array('sku', 'num');
        }
    }

    //获取延迟数据
    public static function getLasy($skus_str)
    {
        if($skus_str)
        {
            $now = date("Y-m-d H:i:s");
            $erp_sql = "
                select
                    upper(orders_sku) as orders_sku,
                    sum(item_count) as totle
                from
                    erp_orders
                join
                    erp_orders_products
                on
                    erp_orders.erp_orders_id=erp_orders_products.erp_orders_id
                where
                    erp_orders_products.orders_sku
                in(
                    {$skus_str}
                )and
                    orders_out_time>='".date('Y-m-d H:00:00',strtotime($now))."'
                and
                    orders_out_time<='".$now."'
                and
                    orders_status=5";

            return DB::query(Database::SELECT,$erp_sql)->execute('readonlyerp')->as_array('sku');
        }
    }

    //获取字段注释表的value值
    public static function getStatusdicsValue($table,$column,$number=NULL,$type=1)
    {
        $re = DB::select('number','value')->from('statusdics')
            ->where('table','=',$table)
            ->where('colum_name','=',$column)
            ->order_by('number','ASC');

        if($number)
        {
            if(is_array($number))
            {
                $re->where('number','IN',array_values($number));

                if($type)
                    return $re->execute('newerp')->as_array('number');
                else
                    return $re->execute('newerp')->as_array('number','value');
            }
            else
            {
                $re->where('number','=',$number);
                return $re->execute('newerp')->get('value','');
            }
        }

        if($type)
            return $re->execute('newerp')->as_array('number');
        else
            return $re->execute('newerp')->as_array('number','value');
    }

    //获取sku状态或者所有sku
    public static function getSkuStatusOrSkus($sku_arr)
    {
        $re = DB::select('sku','status')->from('ldt_skus');

        if(empty($sku_arr))
        {
            if(is_array($sku_arr))
                $re->where('sku','in',$sku_arr);
            else
                $re->where('sku','=',$sku_arr);

            return $re->execute('newerp')->as_array('sku','status');
        }else{
            return $re->execute('newerp')->as_array(NULL,'sku');
        }
    }

    //获取出库记录
    public static function get_delivery_nums($begin,$end,array $sku_arr)
    {
        return DB::select(DB::expr('upper(sku) as sku'),DB::expr("sum(quantity) as num"))->from("deliveryorders")
            ->where('delivery_date','>',$begin)->where('delivery_date','<=',$end)
            ->where('sku','in',$sku_arr)->group_by('sku')->execute('newerp')->as_array('sku','num');
    }

    //获取入库记录
    public static function get_warehouse_nums($begin,$end,array $sku_arr)
    {
        return DB::select(DB::expr('upper(sku) as sku'),DB::expr("sum(warehouse_quantity) as num"))->from("warehouseorders")
            ->where('warehouse_date','>',$begin)->where('warehouse_date','<=',$end)
            ->where('sku','in',$sku_arr)->group_by('sku')->execute('newerp')->as_array('sku','num');
    }

    //统计盘点和未盘点和紧急盘点的数目
    public static function getRecord()
    {
        return DB::select('status',DB::expr("count(id) as num"))
            ->from("ldt_skus")->group_by("status")
            ->execute('newerp')->as_array('status');
    }

    //上传盘点单
    public static function upload($data,$time)
    {
        $error = '';
        $lines=array();
        $count = count($data);
        $user_id = User::active_user()->id;

        $skus = array();
        for($i=2;$i<=$count;$i++)
        {
            $skus[]=trim($data[$i]['A']);
        }
        $sec = time();
        $end   = date('Y-m-d',strtotime("+1 day",$sec));
        $begin = date('Y-m-01 00:00:00',$sec);
        $lists = self::install_lists($begin,$end,$skus);
        $storages=Skusystemapi::getSkuPlaces($skus);
        $storages = array_combine(Arr::pluck($storages,'sku'),$storages);
        $have = DB::select('sku','status')->from('ldt_skus')
            ->where('sku','in',$skus)->execute('newerp')->as_array('sku','status');

        for($i=2;$i<=$count;$i++){
            $sku=strtoupper(trim($data[$i]['A']));
            $download_time=trim($data[$i]['B']);
            $upload_time=date('Y-m-d H:i:s');
            $quantity=trim($data[$i]['G']);
            $quantity2=trim($data[$i]['E']);
            $pd_time=trim($data[$i]['F']);
            $quantity3=$lists[$sku]['quantity'];
            $num=$quantity-$quantity2;//实际盘点量-下载表格时的库存量
            //$status 3:盘点数量相等，1：盘亏，2：盘赢
            $status=3;
            if($num>0){
                $status=2;
            }elseif ($num<0){
                $status=1;
            }

            $info = array();
            $info['sku_name']           = $lists[$sku]['sku_name'];
            $info['sku_standard']       = '';
            $info['storage_position']   = $storages[$sku]['place'];

            try
            {
                list($insert_id,) = DB::insert('pandians',
                    array('sku','sku_name','storage_position','sku_standard','quantity','quantity2','quantity3',
                        'pd_time','op_time','download_time','upload_time','user_id','status','num'))
                    ->values(array($sku,$lists[$sku]['sku_name'],$info['storage_position'],$info['sku_standard'],$quantity,
                        $quantity2,$quantity3,$pd_time,$time,$download_time,$upload_time,$user_id,$status,$num))
                    ->execute('newerp');
            }
            catch(Exception $e)
            {
                $error .= '第'.$i.'行SKU:'.$sku."当天已经插入<br/>";
            }

            if(!$insert_id){
                $lines['num'][]=$i;
                continue;
            }else{
                $condition=array('sku'=>$sku,'delivery_date'=>$download_time,'quantity'=>abs($num),'order_id'=>0);
                $condition=array_merge($condition,$info);
                //获取系统默认单价
                $supplier = new Service_Common();
                $result = json_encode( $supplier->get_sku_price($sku));
                if($num>0){
                    //盘赢自动入库
                    $condition['single_price']=$result;
                    $condition['remark']='盘赢自动入库，盘点单id：'.$insert_id;
                    $condition['type']=30;
                    Warehouse::insert_warehouse($condition);

                }else if($num<0) {
                    //盘亏自动出库
                    $condition['single_price']=0;
                    $condition['remark']='盘亏自动出库，盘点单id：'.$insert_id;
                    $condition['type']=20;
                    $condition['orders_products_id']=0;
                    Delivery::insert_delivery1($condition);
                }

                if(isset($have[$sku]))
                    self::updateStatus($sku,$have[$sku]);
            }
        }
        $lines['error'] = empty($error)?'':"<br/>".$error."以上异常SKU均未上传成功<br/>";
        return $lines;
    }
    //盘点后更新状态
    public static function updateStatus($sku,$st)
    {
        if($st==3) $st=4;else $st=2;
        DB::update('ldt_skus')->set(array('status'=>$st))
            ->where('sku','=',$sku)->execute('newerp');
    }

    //盘盈盘亏插入数据
    public static function insertPanResult($num,$values)
    {
        if($num>0)
            Warehouse::insert_warehouse($values);
        else
            if($num<0)
                Delivery::insert_delivery1($values);
    }

    //获取一个数组的字符串
    public static function getArrStr($arrs)
    {
        $re = '';
        if($arrs&&is_array($arrs))
        {
            foreach($arrs as $str)
            {
                $re .= $re?",'".$str."'":"'".$str."'";
            }
        }
        return $re;
    }

    //按老erp拆单
    public static function bomb($tbl, $time=NULL)
    {
        $codes = DB::select('id','code')->from('erp_code')->execute('readonlyerp')->as_array('id','code');

        $haves = DB::select('sku','count_date','sale')->from($tbl.'_sales')
            ->where('sku','in',$codes);
        if($time)$haves->where('count_date','>=',$time);
        $haves = $haves->execute('newerp')->as_array();

        if(empty($haves))return;

        $have_codes = array();
        foreach($haves as $have)
        {
            if(empty($have_codes))
            {
                if(!in_array($have['sku'],$have_codes))
                {
                    $have_codes[] = $have['sku'];
                }
            }else{
                $have_codes[] = $have['sku'];
            }
        }

        $codes = DB::select('id','code')->from('erp_code')
            ->where('code','in',$have_codes)
            ->execute('readonlyerp')
            ->as_array('code','id');

        $infos = DB::select('sku', 'number', 'code_id')->from('erp_code_number')
            ->where('code_id','in',$codes)
            ->execute('readonlyerp')->as_array();

        $sku_codes_num = array();
        foreach($infos as $info)
        {
            $sku_codes_num[$info['code_id']][$info['sku']]['number'] = $info['number'];
        }

        $hs = DB::select('sku','count_date')->from($tbl.'_sales');
        if($time)$hs->where('count_date','>=',$time);

        $hs = $hs->execute('newerp')->as_array();

        if(empty($hs))return;

        $sku_counts = array();
        foreach($hs as $h)
        {
            $sku_counts[$h['count_date']][] = $h['sku'];
        }

        foreach($haves as $have)
        {
            if(!isset($sku_codes_num[$codes[$have['sku']]]))continue;
            $skus_temp = array_keys($sku_codes_num[$codes[$have['sku']]]);

            foreach($skus_temp as $_sku)
            {
                if(isset($sku_counts[$have['count_date']]) && in_array($_sku, $sku_counts[$have['count_date']]))
                {
                    $sql = "UPDATE {$tbl}_sales SET sale = sale + {$sku_codes_num[$codes[$have['sku']]][$_sku]['number']} * {$have['sale']}
                    WHERE sku = '{$_sku}' AND count_date = '{$have['count_date']}'";

                    DB::query(Database::UPDATE,$sql)->execute('newerp');
                }else{
                    $num = (int)$sku_codes_num[$codes[$have['sku']]][$_sku]['number']*(int)$have['sale'];
                    DB::insert("{$tbl}_sales")->columns(array('sku', 'sale', 'count_date', 'add_time'))
                        ->values(array($_sku, $num,$have['count_date'],$have['count_date']))
                        ->execute('newerp');
                }
            }
            DB::delete("{$tbl}_sales")->where('sku','=',$have['sku'])->and_where('count_date','=',$have['count_date'])->execute('newerp');
        }
    }

    //更新只包含%号的订单
    public static function exHand ($tbl, $minDate = NULL) {
        $tbl = $tbl.'_sales';
        $skus = DB::select('id', 'sku', 'sale', 'count_date', 'add_time')->from($tbl);
        $minDate?$skus->where('count_date', '>=', $minDate):'';
        $skus = $skus->where('sku', 'LIKE', "%\%%")
            ->execute('newerp')->as_array();

        foreach ($skus as $sku) {
            if(preg_match("/^[a-zA-Z0-9%]+$/", trim($sku['sku']))) {
                $exs = explode('%', $sku['sku']);
                if(!empty($exs)) {
                    if(preg_match("/^[0-9]+$/", $exs[1])) {
                        $sku['sku']  = $exs[0];
                        $sku['sale'] = $sku['sale'] * $exs[1];
                    }else{
                        $sku['sku']  = $exs[1];
                        $sku['sale'] = $sku['sale'] * $exs[0];
                    }

                    self::justDoIt($tbl, $sku);
                }
            }
        }

        echo "{$tbl}去掉只包含百分号SKU结束\n";
    }

    //去掉外国仓开头(ebay)
    public static function expCn ($tbl, $t = NULL) {
        $tbl = $tbl.'_sales';
        //仅仅包含下划线
        $skus = DB::select('id', 'sku', 'sale', 'count_date', 'add_time')->from($tbl)
            ->where('sku', 'LIKE', "%\_%")
            ->where('sku', 'NOT LIKE', '%+%')
            ->where('sku', 'NOT LIKE', "% X %")
            ->where('sku', 'NOT LIKE', "%(%")
            ->execute('newerp')->as_array();

        foreach ($skus as $sku) {
            $exs = explode('_', $sku['sku']);
            if(!empty($exs)) {
                if(preg_match("/^[a-zA-Z0-9]+$/", trim($exs[1]))) {
                    $sku['sku']  = trim($exs[1]);
                    $sku['sale'] = $sku['sale'];

                    self::justDoIt($tbl, $sku);
                }
            }
        }

        //包含下划线和加号
        $skus = DB::select('id', 'sku', 'sale', 'count_date', 'add_time')->from($tbl)
            ->where('sku', 'LIKE', "%\_%")
            ->where('sku', 'LIKE', '%+%')
            ->where('sku', 'NOT LIKE', "% X %")
            ->where('sku', 'NOT LIKE', "%(%")
            ->execute('newerp')->as_array();

        foreach ($skus as $sku) {
            $exs = explode('_', $sku['sku']);
            if(!empty($exs)) {
                $exs = explode('+', $exs[1]);
                if(!empty($exs)) {
                    $sale = $sku['sale'];
                    foreach($exs as $e) {
                        if(preg_match("/^[a-zA-Z0-9]+$/", trim($e))) {
                            $sku['sku']  = trim($e);
                            $sku['sale'] = $sale;

                            self::justDoIt($tbl, $sku);
                        }
                    }
                }
            }
        }

        //包含下划线和和乘号
        $skus = DB::select('id', 'sku', 'sale', 'count_date', 'add_time')->from($tbl)
            ->where('sku', 'LIKE', "%\_%")
            ->where('sku', 'LIKE', '% X %')
            ->where('sku', 'NOT LIKE', '%+%')
            ->where('sku', 'NOT LIKE', "%(%")
            ->execute('newerp')->as_array();

        foreach ($skus as $sku) {
            $exs = explode('_', $sku['sku']);
            if(!empty($exs)) {
                $exs = explode(' X ', $exs[1]);
                if(!empty($exs)) {
                    if(preg_match("/^[a-zA-Z0-9]+$/", trim($exs[0]))) {
                        $sku['sku']  = trim($exs[0]);
                        $exs[1] = isset($exs[1])?$exs[1]:1;
                        $sku['sale'] = $sku['sale'] * $exs[1];

                        self::justDoIt($tbl, $sku);
                    }
                }
            }
        }

        //包含下划线乘号和加号
        $skus = DB::select('id', 'sku', 'sale', 'count_date', 'add_time')->from($tbl)
            ->where('sku', 'LIKE', "%\_%")
            ->where('sku', 'LIKE', '%+%')
            ->where('sku', 'LIKE', "% X %")
            ->where('sku', 'NOT LIKE', "%(%")
            ->execute('newerp')->as_array();

        foreach ($skus as $sku) {
            $exs = explode('_', $sku['sku']);
            if(!empty($exs)) {
                $sale = $sku['sale'];
                $exs = explode('+', $exs[1]);
                if(!empty($exs)) {
                    foreach($exs as $ex) {
                        $ee = explode(' X ', $ex);
                        if(!empty($ee)) {
                            if(preg_match("/^[a-zA-Z0-9]+$/", trim($ee[0]))) {
                                $sku['sku']  = trim($ee[0]);
                                $ee[1] = isset($ee[1])?$ee[1]:1;
                                $sku['sale'] = $sale * $ee[1];

                                self::justDoIt($tbl, $sku);
                            }
                        }
                    }
                }
            }
        }

        echo "{$tbl}外国仓结束\n";
    }

    //速卖通平台去掉加号
    public static function expSum ($tbl) {
        $tbl = $tbl."_sales";
        //去掉仅仅包含+号的
        $sql = "SELECT id, sku, sale, count_date, add_time
        FROM `{$tbl}`
        WHERE `sku` LIKE '%+%'
        AND sku NOT LIKE '%\%%'
        AND sku NOT LIKE '%(%'
        AND sku NOT LIKE '% X %'";

        $skus = DB::query(Database::SELECT, $sql)->execute('newerp')->as_array();

        foreach ($skus as $sku) {
            $exs = explode('+', $sku['sku']);
            $sale = $sku['sale'];
            if(!empty($exs)) {
                foreach($exs as $e) {
                    if(preg_match("/^[a-zA-Z0-9]+$/", trim($e))) {
                        $sku['sku']  = trim($e);
                        $sku['sale'] = $sale;

                        self::justDoIt($tbl, $sku);
                    }
                }
            }
        }

        echo "{$tbl}去掉加号结束\n";
        self::expHandOnly($tbl);
        self::expSumAndHand($tbl);
    }

    //仅仅去掉百分号
    public static function expHandOnly($tbl) {
        //去掉仅仅包含%号的
        $sql = "SELECT id, sku, sale, count_date, add_time
        FROM `{$tbl}`
        WHERE `sku` NOT LIKE '%+%'
        AND sku LIKE '%\%%'
        AND sku NOT LIKE '%(%'
        AND sku NOT LIKE '% X %'";

        $skus = DB::query(Database::SELECT, $sql)->execute('newerp')->as_array();

        foreach ($skus as $sku) {
            $exs = explode('%', $sku['sku']);
            if(!empty($exs)) {
                if(preg_match("/^[0-9]+$/", trim($exs[1]))) {
                    $sku['sku']  = trim($exs[0]);
                    $sku['sale'] = $sku['sale'] * $exs[1];

                    self::justDoIt($tbl, $sku);
                }else {
                    $sku['sku']  = trim($exs[1]);
                    $sku['sale'] = $sku['sale'] * $exs[0];

                    self::justDoIt($tbl, $sku);
                }
            }
        }

        echo "{$tbl}去掉加号结束\n";
    }

    //去掉加号和百分号
    public static function expSumAndHand ($tbl) {
        //去掉仅仅包含+号和%的
        $sql = "SELECT id, sku, sale, count_date, add_time
        FROM `{$tbl}`
        WHERE `sku` LIKE '%+%'
        AND sku LIKE '%\%%'
        AND sku NOT LIKE '%(%'
        AND sku NOT LIKE '% X %'";

        $skus = DB::query(Database::SELECT, $sql)->execute('newerp')->as_array();

        foreach ($skus as $sku) {
            $ee = explode('+', $sku['sku']);
            if(!empty($ee)) {
                $sale = $sku['sale'];
                foreach($ee as $e) {
                    if(preg_match("/^[a-zA-Z0-9%]+$/", trim($e))) {

                        $exs = explode('%', trim($e));
                        if(!empty($exs)) {
                            if(preg_match("/^[0-9]+$/", $exs[1])) {
                                $sku['sku']  = $exs[0];
                                $sku['sale'] = $sale * $exs[1];
                            }else{
                                $sku['sku']  = $exs[1];
                                $sku['sale'] = $sale * $exs[0];
                            }

                            self::justDoIt($tbl, $sku);
                        }
                    }
                }
            }
        }

        echo "{$tbl}去掉加号和百分号结束\n";
    }

    //ebay平台多加号和乘号
    public static function expmul ($tbl) {
        $tbl = $tbl."_sales";
        //去掉仅仅包含+号和%的
        $sql = "SELECT id, sku, sale, count_date, add_time
        FROM `{$tbl}`
        WHERE `sku` LIKE '%+%'
        AND sku LIKE '%\%%'
        AND sku NOT LIKE '%(%'
        AND sku NOT LIKE '% X %'";

        $skus = DB::query(Database::SELECT, $sql)->execute('newerp')->as_array();

        foreach ($skus as $sku) {
            $ee = explode('+', $sku['sku']);
            if(!empty($ee)) {
                $sale = $sku['sale'];
                foreach($ee as $e) {
                    if(preg_match("/^[a-zA-Z0-9%]+$/", trim($e))) {
                        $exs = explode('%', trim($e));
                        if(!empty($exs)) {
                            if(preg_match("/^[0-9]+$/", $exs[1])) {
                                $sku['sku']  = $exs[0];
                                $sku['sale'] = $sale * $exs[1];
                            }else{
                                $sku['sku']  = $exs[1];
                                $sku['sale'] = $sale * $exs[0];
                            }
                            self::justDoIt($tbl, $sku);
                        }
                    }
                }
            }
        }

        echo "{$tbl}去掉加号和百分号结束\n";
    }


    public static function justDoIt ($tbl, $sku) {
        $have = DB::select('id', 'sku', 'sale')->from($tbl)
            ->where('sku','=',$sku['sku'])
            ->where('count_date', '=', $sku['count_date'])
            ->execute('newerp')
            ->current();

        if(empty($have)) {
            DB::insert($tbl)->columns(array('sku', 'sale', 'count_date', 'add_time'))
                ->values(array($sku['sku'], $sku['sale'], $sku['count_date'], $sku['add_time']))
                ->execute('newerp');
        } else {
            $sql = "UPDATE {$tbl} SET sale = sale + {$sku['sale']} WHERE `id` = '{$have['id']}'";
            ;
            DB::query(Database::UPDATE,$sql)->execute('newerp');
        }

        DB::delete($tbl)->where('id','=',$sku['id'])->execute('newerp');
    }

    //
    public function deal_complex_sku($arr)
    {
        preg_match_all("/.*?\((.*?)\)\s*X\s*(\d{1,}).*?/si",$arr[0],$result);
        if(!empty($result[0])){
            $num = count($result[1]);
            for($i = 0;$i<$num;$i++){

                $arr3 = explode("+",$result[1][$i]);
                $num3 = count($arr3);
                for($j = 0;$j<$num3;$j++){
                    $arr2[] = $arr3[$j]." X ".$result[2][$i];
                }
            }
            foreach($arr2 as &$ar){
                $ar = trim($ar);
            }
            $result1 = explode("+",preg_replace("/\(.*?\)\s*X\s*(\d{1,})\s*/si","",$arr[0]));
            if(!empty($result1)){
                foreach($result1 as $key => &$values){
                    if(!empty($values)){
                        $values = trim($values);
                    }else{
                        unset($result1[$key]);
                    }
                }
                $arr1 = array_merge($result1,$arr2);
            }else{
                $arr1 = $arr2;
            }
        }else{
            $arr1 = explode("+",$arr[0]);
            foreach($arr1 as $key => &$value){
                if(!empty($value)){
                    $value = trim($value);
                }else{
                    unset($arr1[$key]);
                }
            }
        }
        foreach($arr1 as $va){
            if(strpos($va," X ")){
                $arr_max[] = explode(" X ",$va);
            }else{
                $arr_max[] = array(
                    $va,
                    1
                );
            }
        }
        $sku_num = count($arr_max);
        for($k = 0;$k<$sku_num;$k++){
            $deal_result[$k]['sku']              = $arr_max[$k][0];
            $deal_result[$k]['sku_name']         = "";
            $deal_result[$k]['item_count']       = $arr_max[$k][1]*$arr[2];
            $deal_result[$k]['ebay_order_id']    = $arr[3];
            $deal_result[$k]['ordersproduct_id'] = $arr[4];
            $deal_result[$k]['buyer_id']         = $arr[6];
            $deal_result[$k]['sales_account']    = $arr[7];
            $deal_result[$k]['ebay_ordersn']     = $arr[8];
            $deal_result[$k]['pay_id']           = $arr[9];
            $deal_result[$k]['toid']             = $arr[10];
            $deal_result[$k]['oldid']            = $arr[11];
            $deal_result[$k]['toorders']         = $arr[12];
        }
        return $deal_result;
    }

    //洗掉以&%$开头的SKU
    public static function badBegin($tbl=NULL) {
        $tbl = $tbl."_sales";
        //去掉仅仅包含+号的
        $sql = "SELECT id, sku, sale, count_date, add_time
        FROM `{$tbl}`
        WHERE `sku` LIKE '\%%'
        OR sku LIKE '$%'
        OR sku LIKE '&%'";

        $skus = DB::query(Database::SELECT, $sql)->execute('newerp')->as_array();

        foreach ($skus as $sku) {
            if(preg_match("/^[a-zA-Z0-9%$&]+$/", trim($sku['sku']))) {
                $sku['sku'] = str_replace('%', "", trim($sku['sku']));
                $sku['sku'] = str_replace('$', "", trim($sku['sku']));
                $sku['sku'] = str_replace('&', "", trim($sku['sku']));

                if(preg_match("/^[a-zA-Z0-9]+$/", trim($sku['sku']))) {
                    $sku['sku'] = trim($sku['sku']);

                    self::justDoIt($tbl, $sku);
                }
            }
        }
        echo "{$tbl}去掉特殊符号开头结束\n";
    }

    //活动SKU剔除
    public static function activeOut ($tId, $t = NULL) {
        $tbls = array('1'=>'ebay_sales',2=>'smt_sales',3=>'amz_sales',4=>'wish_sales');
        $actives = DB::select()->from('active')
            ->where('stage', '=', $tId)
            ->where('status', '=', 1);
        if($t)$actives->where('add_time', '>=', $t);
        $actives = $actives->execute('newerp')->as_array();

        if(empty($actives))return;

        $now_src = strtotime(date("Y-m-d"));
        foreach($actives as $active) {
            $beg_src = strtotime($active['begin']);
            $end_src = strtotime($active['end']);
            //已经结束的活动，取活动后一天的销量作为活动天的销量
            if(strtotime($active['end']) < $now_src) {
                //判断昨天是不是活动，如果是活动，则用活动前一天销量填充
                if(date("Y-m-d", $end_src) == date("Y-m-d", strtotime("-1 day"))) {
                    $sale_date = date("Y-m-d", strtotime("-1 day", $beg_src));
                }else{
                    //取活动结束后的一天的销量作为活动每天的销量
                    $sale_date = date("Y-m-d", strtotime("1 day", $end_src));
                }
            }else{
                if($beg_src >= $now_src)continue;
                //还没有结束的活动用前一天填充活动天的销量
                $sale_date = date("Y-m-d", strtotime("-1 day", $beg_src));
            }

            $skus = DB::select('sku')->from('active_skus')
                ->where('active_id', '=', $tId)
                ->execute('newerp')
                ->as_array(NULL,'sku');

            if(empty($skus)) continue;

            $skus_sale = DB::select('sku','sale')->from($tbls[$tId])
                ->where('sku', 'IN', $skus)
                ->where('count_date', '=', $sale_date)
                ->execute('newerp')
                ->as_array('sku', 'sale');

            for($bd = $beg_src; $bd <= $end_src; $bd += 86400) {
                $cd = date("Y-m-d", $bd);
                if($cd == date("Y-m-d"))continue;

                $add_time = date("Y-m-d", strtotime("1 day", strtotime($cd)));

                foreach($skus as $sku) {
                    if(isset($skus_sale[$sku])) {
                        $sql = "INSERT INTO `{$tbls[$tId]}` (
                            `id`, `sku`, `sale`, `count_date`, `add_time`
                        ) SELECT
                            `data`.id, '{$sku}', '{$skus_sale[$sku]}', '{$cd}', '{$add_time}'
                        FROM
                            (SELECT TRUE) `temp`
                                LEFT JOIN (SELECT
                                id
                            FROM
                                `{$tbls[$tId]}`
                            WHERE
                                `sku` = '{$sku}'
                            AND `count_date` = '{$cd}'
                        ) `data` ON
                            TRUE
                       ON DUPLICATE KEY UPDATE
                       `sale` = '{$skus_sale[$sku]}'";

                        DB::query(Database::INSERT, $sql)->execute('newerp');
                    }
                }
            }
        }
    }
}